﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using NLog;

namespace Tab2Sql.Classes.Core
{
    /// <summary>
    /// Main class to translate DataTable to Sql Statement (as a string). Is instantiated by the xTab2Sql object.
    /// 
    /// </summary>
    public class xDataTable2Sql
    {

        private static Logger logger = LogManager.GetCurrentClassLogger ();
        System.Diagnostics.Stopwatch swatch;
        const string myTab = "    ";
        const string myGap = " ";
        private Options options;
        private xTab2Sql xTs;
        /// <summary>
        /// This is the number of characters at which datatypes start in a create table statement.
        /// e.g.
        /// CREATE TABLE foo 
        ///       (
        ///         [RowNo]                                            INTEGER,
        ///         [Name]                                             VARCHAR(24)
        ///         ---------------------------------------------------this is the indendentation level
        ///       )   
        /// </summary>
        private int indentationLevel;

        /// <summary>
        /// MAIN CONSTRUCTOR
        /// </summary>
        /// <param name="xTSql"></param>
        public xDataTable2Sql (xTab2Sql xTSql)
        {
            indentationLevel = 47;
            swatch = new System.Diagnostics.Stopwatch ();
            swatch.Start ();
            logger.Trace ("{0}: Constructor called. ", swatch.ElapsedMilliseconds);
            
            
            xTs = xTSql;
            options = xTs.Options;
            if (options.StatementType == SqlStatementType.Standard)
            {
                if (xTs.RowCount > options.MultiInsertThreshold)
                {
                    options.StatementType = SqlStatementType.MultiInsert;
                }
            }
        }

        /// <summary>
        /// Sql Statement, in full. Includes Create Table and Inserts.
        /// </summary>
        public String FullStatement {
            get {
                switch (options.StatementType)
                {
                case SqlStatementType.Standard:
                    return StatementHeader () + CreateTable ().ToString () + InsertInto ().ToString ();
                case SqlStatementType.MultiInsert:
                    return StatementHeader () + CreateTable ().ToString () + InsertIntoSelects ().ToString ();
                case SqlStatementType.IntoExisting:
                    return "NOT IMPLEMENTED YET";
                case SqlStatementType.DataMode:
                    return "NOT IMPLEMENTED YET";
                default:
                    return "NOT IMPLEMENTED YET";
                }
            }
        }

        /// <summary>
        /// Drop Table statement.
        /// </summary>
        private String dropTable {
            get {
                switch (this.xTs.Options.SqlVariant)
                {
                case SqlVariants.TSql:
                    return string.Format ("BEGIN TRY \n    DROP TABLE {0};\nEND TRY\nBEGIN CATCH\n    PRINT 'Drop failed (continuing)';\nEND CATCH\nGO\n", this.xTs.TableName);
                case SqlVariants.Sqlite:
                    return string.Format ("BEGIN TRANSACTION;\n    DROP TABLE IF EXISTS {0};\n    ", this.xTs.TableName.Replace ("#", string.Empty));
                case SqlVariants.Informix:
                    return "";
                case SqlVariants.ANSI:
                    return "";
                case SqlVariants.SqlAnywhere:
                    return string.Format ("BEGIN\n\tDROP TABLE IF EXISTS {0};\n\t", this.xTs.TableName);
                case SqlVariants.MSAccess:
                    return "";
                default:
                    return string.Format ("BEGIN TRY \n    DROP TABLE {0};\nEND TRY\nBEGIN CATCH\n    PRINT 'Drop failed (continuing)';\nEND CATCH\nGO\n", this.xTs.TableName);
                }
                
                
            }
        }

        private String initialStatements {
            get {
                switch (this.xTs.Options.SqlVariant)
                {
                case SqlVariants.TSql:
                    return string.Format ("SET NOCOUNT ON\nGO\nSET DATEFORMAT DMY\nGO\n");
                case SqlVariants.Sqlite:
                    return "";
                case SqlVariants.Informix:
                    return "";
                case SqlVariants.ANSI:
                    return "";
                case SqlVariants.SqlAnywhere:
                    return "";
                case SqlVariants.MSAccess:
                    return "";
                default:
                    return string.Format ("SET NOCOUNT ON\nGO\nSET DATEFORMAT DMY\nGO\n");
                }
                
                
            }
        }

        private String createTableBase {
            get {
                switch (this.xTs.Options.SqlVariant)
                {
                case SqlVariants.TSql:
                    return string.Format ("CREATE TABLE {0}", this.xTs.TableName);
                case SqlVariants.Sqlite:
                    string tmpSqliteName = this.xTs.TableName;
                    tmpSqliteName = tmpSqliteName.Replace ("#", string.Empty);
                    return string.Format ("CREATE TABLE {0}", tmpSqliteName);
                case SqlVariants.Informix:
                    string tmpInformixName = this.xTs.TableName;
                    tmpInformixName = tmpInformixName.Replace ("#", "");
                    return string.Format ("CREATE TEMPORARY TABLE {0}", tmpInformixName);
                case SqlVariants.ANSI:
                    return "";
                case SqlVariants.SqlAnywhere:
                    return string.Format ("CREATE TABLE {0}", this.xTs.TableName);
                case SqlVariants.MSAccess:
                    return "";
                default:
                    return string.Format ("CREATE TABLE {0}", this.xTs.TableName);
                }
                
                
            }
        }






        private string StatementHeader ()
        {
            string resultString = initialStatements;
            resultString += "";
            
            resultString += dropTable;
            return resultString;
        }
        /// <summary>
        /// CREATE TABLE Statement
        /// </summary>
        /// <returns></returns>
        public StringBuilder CreateTable ()
        {
            
            StringBuilder cts = new StringBuilder ();
            cts.Append (createTableBase);
            cts.Append (Environment.NewLine + "\t(\t\t");
            // then output "[RNo] INTEGER"
            if (options.IncludeRowNumber == RowID.AddRowNumber)
            {
                cts.Append (Environment.NewLine + myTab + myTab);
                
                int fixedLen = indentationLevel;
                string rowNoName = "RowNo";
                if (xTs.Columns.Contains (rowNoName) || xTs.Columns.Contains ("[" + rowNoName + "]"))
                {
                    rowNoName = "RowNo02087712260";
//                    try {rowNoName =  getNewNameForRowNoColumn();}
//                  catch(Exception ex) { 
//                      logger.Error(ex.Message);
//                      rowNoName="RowNo02087712260";
//                  }
                }
                cts.Append ("[" + rowNoName + "]" + DGen.DText.Replicate (myGap, fixedLen) + "INTEGER,");
            }
            if (options.IncludeRowNumber == RowID.AddRowGUID)
            {
                cts.Append (Environment.NewLine + myTab + myTab);
                int fixedLen = indentationLevel;
                string rowIDName = "RID";
                cts.Append ("[" + rowIDName + "]" + DGen.DText.Replicate (myGap, fixedLen) + "CHAR(32),");
                
            }
            int i = 0;
            //columnName number
            foreach (DataColumn dc in xTs.Columns)
            {
                string columnName = dc.ToString ();
                if (columnName.Length > 0 && i < xTs.ColumnTypes.Count ())
                {
                    
                    int columnLen = Math.Max ((indentationLevel + 3) - columnName.Length, 2);
                    cts.Append (Environment.NewLine + myTab + myTab);
                    if (xTs.Options.SqlVariant == SqlVariants.SqlAnywhere)
                    {
                        cts.Append ("\t");
                    }
                    string columnNameWithBrackets = columnName;
                    if (!columnNameWithBrackets.Contains ("[") && !columnNameWithBrackets.Contains ("]"))
                    {
                        columnNameWithBrackets = "[" + columnNameWithBrackets.Trim () + "]";
                    }
                    cts.Append (columnNameWithBrackets);
                    cts.Append (DGen.DText.Replicate (myGap, columnLen));
                    DataTypes tmpDataType = xTs.ColumnTypes[i];
                    string columnScale = string.Empty;
                    switch (xTs.ColumnTypes[i])
                    {
                    case DataTypes.unknown:
                        tmpDataType = DataTypes.varchar;
                        xTs.ColumnTypes[i] = DataTypes.varchar;
                        cts.Append (tmpDataType.ToString ().ToUpper ());
                        cts.Append ("(255)");
                        break;
                    case DataTypes.datetime:
                        tmpDataType = DataTypes.datetime;
                        cts.Append (tmpDataType.ToString ().ToUpper ());
                        break;
                    case DataTypes.integer:
                        tmpDataType = DataTypes.integer;
                        cts.Append (tmpDataType.ToString ().ToUpper ());
                        break;
                    case DataTypes.bigint:
                        tmpDataType = DataTypes.bigint;
                        cts.Append (tmpDataType.ToString ().ToUpper ());
                        break;
                    case DataTypes.numeric:
                        tmpDataType = DataTypes.numeric;
                        cts.Append (tmpDataType.ToString ().ToUpper ());
                        columnScale = getColumnScale (xTs.Highest[i]);
                        cts.Append (columnScale);
                        break;
                    case DataTypes.percentage:
                        tmpDataType = DataTypes.numeric;
                        columnScale = "(9,5)";
                        cts.Append (tmpDataType.ToString ().ToUpper ());
                        cts.Append (columnScale);
                        break;
                    case DataTypes.varchar:
                        tmpDataType = DataTypes.varchar;
                        
                        int LongestLine = Math.Max (xTs.LongestLine[i] + 5, dc.MaxLength + 3);
                        if (LongestLine > 3999)
                        {
                            switch (this.xTs.Options.SqlVariant)
                            {
                            case SqlVariants.TSql:
                                cts.Append (tmpDataType.ToString ().ToUpper ());
                                cts.Append ("(MAX)");
                                break;
                            case SqlVariants.SqlAnywhere:
                                cts.Append ("LONG ");
                                cts.Append (tmpDataType.ToString ().ToUpper ());
                                break;
                            case SqlVariants.Sqlite:
                                cts.Append (" TEXT ");
                                break;
                                
                            }
                            
                        } else
                        {
                            cts.Append (tmpDataType.ToString ().ToUpper ());
                            cts.Append ("(" + LongestLine + ")");
                        }
                        break;
                    default:
                        tmpDataType = DataTypes.varchar;
                        cts.Append (tmpDataType.ToString ().ToUpper ());
                        break;
                    }
                    cts.Append (",");
                    i++;
                }
                
            }
            
            cts.Remove (cts.Length - 1, 1);
            switch (xTs.Options.SqlVariant)
            {
            case SqlVariants.TSql:
                cts.AppendLine (Environment.NewLine + myTab + ");");
                break;
            case SqlVariants.SqlAnywhere:
                cts.AppendLine (Environment.NewLine + myTab + myTab + ");");
                break;
            case SqlVariants.Sqlite:
                cts.AppendLine (Environment.NewLine + myTab + myTab + ");");
                break;
            default:
                cts.AppendLine (Environment.NewLine + myTab + myTab + ");");
                break;
            }
            return cts;
        }

        private string InsertIntoBase {
            get {
                string columnList = "";
                if (options.UseColumnHeadings == ColumnHeadings.IncludeColumnHeadings)
                {
                    columnList = getColumnList ();
                }
                string prefix = "";
                
                switch (this.xTs.Options.SqlVariant)
                {
                case SqlVariants.TSql:
                    prefix = string.Format ("INSERT INTO {0} {1} VALUES (", xTs.TableName, columnList);
                    break;
                case SqlVariants.Sqlite:
                    string tmpSqliteName = this.xTs.TableName;
                    tmpSqliteName = tmpSqliteName.Replace ("#", string.Empty);
                    prefix = string.Format ("\tINSERT INTO {0} {1} VALUES (", tmpSqliteName, columnList);
                    break;
                case SqlVariants.Informix:
                    string tmpInformixName = this.xTs.TableName;
                    tmpInformixName = tmpInformixName.Replace ("#", "");
                    prefix = string.Format ("INSERT INTO {0} {1} VALUES (", tmpInformixName, columnList);
                    break;
                case SqlVariants.ANSI:
                    break;
                case SqlVariants.SqlAnywhere:
                    prefix = string.Format ("\tINSERT INTO {0} {1} VALUES (", xTs.TableName, columnList);
                    
                    break;
                case SqlVariants.MSAccess:
                    
                    break;
                default:
                    prefix = string.Format ("INSERT INTO {0} {1} VALUES (", xTs.TableName, columnList);
                    break;
                }
                
                return prefix;
            }
        }
        /// <summary>
        /// INSERT INTO...blah.
        /// </summary>
        public StringBuilder InsertInto ()
        {
            logger.Trace ("{0}: InsertInto starting.", swatch.ElapsedMilliseconds);
            int batchSize = options.BatchSize;
            int RowNo = 0;
            int rowsLeftInBatch = batchSize;
            int batchNumber = 0;
            int totalBatches = Convert.ToInt32 (Math.Round ((double)xTs.RowCount / (double)batchSize, 0));
            StringBuilder insertStr = new StringBuilder (20000);
            
            string prefix = InsertIntoBase;
            string suffix = ");" + Environment.NewLine;
            insertStr.Capacity = xTs.RowCount * xTs.ColumnCount * 128;
            DataTypes[] dtype = new DataTypes[xTs.ColumnCount];
            insertStr.Append (globalPrefix (batchNumber, totalBatches));
            foreach (DataRow r in xTs.Rows)
            {
                RowNo++;
                if (RowNo % 200 == 0)
                {
                    logger.Trace ("{0}: InsertInto Main Loop. RowNo {1}.", swatch.ElapsedMilliseconds, RowNo);
                    
                }
                
                RowNo = writeLine (RowNo, ref insertStr, prefix, suffix, ref dtype, r);
                
                if (options.IncludeQueryFooter == true)
                {
                    doFooter ();
                }
            }
            if (xTs.Options.SqlVariant == SqlVariants.SqlAnywhere)
            {
                insertStr.Append ("\nEND;\n");
            }
            return insertStr;
            
        }

        private void doFooter ()
        {
            logger.Info ("Unimplemented function called - doFooter()");
            throw new NotImplementedException ();
        }
        /// <summary>
        /// INSERT INTO foo SELECT a,b,c UNION ALL SELECT a,b,c...blah.
        /// </summary>
        public StringBuilder InsertIntoSelects ()
        {
            logger.Trace ("{0}: InsertIntoSelects starting.", swatch.ElapsedMilliseconds);
            
            DataTypes[] dtype = new DataTypes[xTs.ColumnCount];
            int batchSize = options.BatchSize;
            int RowNo = 0;
            StringBuilder insertStr = new StringBuilder ();
            string suffix = "  " + Environment.NewLine + "UNION ALL ";
            
            string columnList = "";
            if (options.UseColumnHeadings == ColumnHeadings.IncludeColumnHeadings)
            {
                columnList = getColumnList ();
            }
            string prefix = Environment.NewLine + "SELECT    ";
            
            int rowsLeftInBatch = batchSize;
            int batchNumber = 0;
            int totalBatches = Convert.ToInt32 (Math.Round ((double)xTs.RowCount / (double)batchSize, 0));
            insertStr.Append (globalPrefix (batchNumber, totalBatches));
            foreach (DataRow r in xTs.Rows)
            {
                RowNo++;
                if (RowNo % 200 == 0)
                {
                    logger.Trace ("{0}: InsertInto Main Loop. RowNo {1}.", swatch.ElapsedMilliseconds, RowNo);
                    
                }
                if (rowsLeftInBatch == -1)
                {
                    batchNumber++;
                    rowsLeftInBatch = batchSize;
                    insertStr.AppendLine (globalPrefix (batchNumber, totalBatches));
                    writeLine (RowNo, ref insertStr, prefix, suffix, ref dtype, r, batchNumber, rowsLeftInBatch);
                    rowsLeftInBatch--;
                } else
                {
                    writeLine (RowNo, ref insertStr, prefix, suffix, ref dtype, r, batchNumber, rowsLeftInBatch);
                    rowsLeftInBatch--;
                }
                
                
                
            }
            
            return insertStr;
            
        }

        private int writeLine (int RowNo, ref StringBuilder insertStr, string prefix, string suffix, ref DataTypes[] dtype, DataRow r, int batchNumber, int rowsLeftInBatch)
        {
            var x = new string[xTs.ColumnCount];
            int columnsWritten = 0;
            int[] LongestLines = new int[xTs.ColumnCount];
            insertStr.Append (prefix);
            if (options.IncludeRowNumber == RowID.AddRowNumber)
            {
                insertStr.Append (RowNo.ToString ().PadRight (xTs.RowCount.ToString ().Length + 3) + ",");
            }
            if (options.IncludeRowNumber == RowID.AddRowGUID)
            {
                string myGuid = Guid.NewGuid ().ToString ();
                insertStr.Append (myGuid.PadRight (xTs.RowCount.ToString ().Length + 3) + ",");
            }
            for (int i = 0; i < Math.Min (xTs.ColumnCount, xTs.ColumnTypes.Count ()); i++)
            {
                try
                {
                    x[i] = r.ItemArray.GetValue (i).ToString ();
                    dtype[i] = xTs.ColumnTypes[i];
                    LongestLines[i] = xTs.LongestLine[i];
                } catch (Exception ex)
                {
                    Console.WriteLine (ex.Message);
                }
            }
            int i2 = 0;
            foreach (string column_value in x)
            {
                if (column_value == null)
                {
                    
                } else
                {
                    string tmpHolder = column_value.Trim ();
                    tmpHolder = tmpHolder.Replace ("'", "''");
                    if (column_value == null || column_value.Length == 0 || column_value.Trim ().Length == 0 || column_value == "null")
                    {
                        tmpHolder = "null,";
                        tmpHolder = tmpHolder.PadRight (LongestLines[i2] + 3);
                    } else
                    {
                        if (dtype[i2] != DataTypes.integer && dtype[i2] != DataTypes.bigint && dtype[i2] != DataTypes.numeric)
                        {
                            tmpHolder = "'" + tmpHolder + "',";
                            tmpHolder = tmpHolder.PadRight (LongestLines[i2] + 3);
                        } else
                        {
                            tmpHolder = tmpHolder + ",";
                            tmpHolder = tmpHolder.PadRight (LongestLines[i2] + 5);
                        }
                    }
                    if (columnsWritten + 1 == Math.Min (xTs.ColumnCount, options.MaximumColumns))
                    {
                        tmpHolder = tmpHolder.Remove (tmpHolder.LastIndexOf (','), 1).TrimEnd ();
                    }
                    columnsWritten += writeColumn (ref insertStr, tmpHolder);
                    i2++;
                }
            }
            //last line of batch / session
            if (RowNo == xTs.RowCount || rowsLeftInBatch == 0)
            {
                if (options.StatementType == SqlStatementType.Standard)
                {
                    insertStr.Append (suffix);
                }
                if (options.SqlVariant == SqlVariants.SqlAnywhere)
                {
                    insertStr.AppendLine ("\nCOMMIT;");
                }
                if (options.SqlVariant == SqlVariants.TSql)
                {
                    insertStr.AppendLine ("\nCOMMIT;\nGO");
                }
                if (options.SqlVariant == SqlVariants.Sqlite)
                {
                    insertStr.AppendLine (";\nCOMMIT;");
                }
                if (rowsLeftInBatch == 0)
                {
                }
            } else
            {
                insertStr.Append (suffix);
            }
            //if (columnsWritten != xTs.ColumnCount)
            //{
            //    string msg = string.Format("INVALID NUMBER OF COLUMNS OUTPUT! {0} vs {1}", columnsWritten, xTs.ColumnCount);
            //    throw new Exception(msg);
            
            //}
            return RowNo;
        }
        private int writeLine (int RowNo, ref StringBuilder insertStr, string prefix, string suffix, ref DataTypes[] dtype, DataRow r)
        {
            return writeLine (RowNo, ref insertStr, prefix, suffix, ref dtype, r, -1, -1);
        }
        private int writeColumn (ref StringBuilder insertStr, string tmpHolder)
        {
            int success = 0;
            // I can't recall wtf this bit is about 
            // - DL (2011-02-04)
            if (success == 0)
            {
                insertStr.Append (tmpHolder);
                success++;
            }
            return success;
        }

        private string getColumnList ()
        {
            string f = "(";
            foreach (DataColumn dc in xTs.Columns)
            {
                f += dc.ColumnName + ",";
            }
            f = f.Remove (f.Length - 1, 1) + ")";
            return f;
        }


        private string globalPrefix (int batchCount, int totalBatches)
        {
            string resultString = "";
            if (options.StatementType == SqlStatementType.MultiInsert)
            {
                switch (xTs.Options.SqlVariant)
                {
                case SqlVariants.TSql:
                    resultString += string.Format ("/* {0} of {1} */", batchCount, totalBatches);
                    resultString += string.Format ("\nPRINT 'Batch {0} of {1}'", batchCount, totalBatches);
                    resultString += string.Format ("\nBEGIN TRANSACTION \nINSERT INTO {0} ", xTs.TableName);
                    break;
                case SqlVariants.Sqlite:
                    if (batchCount == 0)
                    {
                        resultString += "\nCOMMIT;\n";
                    }
                    resultString += string.Format (" /*================= Batch {0} of {1} (ish) ================*/", batchCount, totalBatches);
                    
                    
                    string tmpSqliteName = this.xTs.TableName;
                    tmpSqliteName = tmpSqliteName.Replace ("#", string.Empty).Trim ();
                    resultString += string.Format ("\nBEGIN TRANSACTION;\nINSERT INTO {0}", tmpSqliteName);
                    break;
                case SqlVariants.Informix:
                    break;
                case SqlVariants.ANSI:
                    break;
                case SqlVariants.SqlAnywhere:
                    resultString += string.Format ("\n/*Batch {0} of {1} */", batchCount, totalBatches);
                    resultString += string.Format ("\nMessage 'Batch {0} of {1}' to client;", batchCount, totalBatches);
                    resultString += string.Format ("\n\tINSERT INTO {0} ", xTs.TableName);
                    break;
                case SqlVariants.MSAccess:
                    break;
                default:
                    resultString += string.Format ("/* {0} of {1} */", batchCount, totalBatches);
                    resultString += string.Format ("\nPRINT 'Batch {0} of {1}'", batchCount, totalBatches);
                    
                    resultString += string.Format ("\nBEGIN TRANSACTION \nINSERT INTO {0} ", xTs.TableName);
                    break;
                }

            }
            if (options.StatementType == SqlStatementType.IntoExisting)
            {

                resultString = "BEGIN TRANSACTION" + Environment.NewLine;
            }
            if (options.StatementType == SqlStatementType.Standard)
            {
                switch (xTs.Options.SqlVariant)
                {
                case SqlVariants.TSql:
                    resultString = "BEGIN TRANSACTION" + Environment.NewLine;
                    break;
                case SqlVariants.SqlAnywhere:
                    resultString = "" + Environment.NewLine;
                    break;
                case SqlVariants.Sqlite:
                    resultString = "" + Environment.NewLine;
                    break;
                default:
                    resultString = "BEGIN TRANSACTION" + Environment.NewLine;
                    break;
                }
            }
            return resultString;
        }













        /// <summary>
        /// Returns a sql style numeric scale - e.g. (9,4);
        /// This needs to be refactored and moved out of this class.
        /// </summary>
        /// <param name="p"></param>
        /// <returns></returns>
        private string getColumnScale (decimal p)
        {
            int Scale = 14;
            int Precision = 5;
            decimal billion = 1000 * 1000 * 1000;
            if (p > billion)
            {
                decimal answer = p / billion;
                decimal n2 = 0;
                int p2 = 1;
                while (answer > n2)
                {
                    n2 = (decimal)Math.Pow (10, p2);
                    p2++;
                }
                
                
                Scale = Scale + p2;
            }
            decimal WholeNumber = Math.Floor (p);
            decimal LeftOver = p - WholeNumber;
            Precision = Math.Max (LeftOver.ToString ().Replace ("0.", String.Empty).Length, 2);
            string rtnString = string.Format ("({0}, {1})", Scale, Precision);
            
            return rtnString;
        }
    }
}
